package com.weixin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IKitingDAO;
import com.weixin.util.C3p0Util;
import com.weixin.vo.Kiting;
import com.weixin.vo.MemberList;
import com.weixin.vo.ZhuanZhang;

public class KitingImpl implements IKitingDAO{
	/**
	 * 将所有的记录查询出来
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<Kiting> findALL() throws SQLException {
		return C3p0Util.qr.query("select * from kiting_table ", new BeanListHandler<Kiting>(Kiting.class));
	}

	/**
	 * 进行分页
	 * @param pageNum  页码
	 * @param pageSize  每页的数据条数
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<Kiting> findBySplit(int pageNum, int pageSize) throws SQLException {
		return C3p0Util.qr.query("select * from kiting_table order by trade_id desc limit ?,?",new BeanListHandler<Kiting>(Kiting.class), (pageNum-1)*pageSize,pageSize);
	}

	/**
	 * 计算总条数
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int findTotal() throws SQLException {
		Connection connection = C3p0Util.getConnection();
        PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement("select count(*) from kiting_table");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int n = resultSet.getInt(1);
        connection.close();
        return n;
	}

	/**
	 * 根据单号删除数据
	 * @param trade_id 单号
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean removeById(int trade_id) throws SQLException {
		return C3p0Util.qr.execute("delete from kiting_table where trade_id=?", trade_id)>0;
	}

	/**
	 * 删除选中的全部
	 * @param trade_ids 选中的所有的 交易单号是一个数组
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean removeSelect(String[] trade_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from kiting_table where trade_id in (");
		List<String> valueList  = new  ArrayList<>();
		 //遍历数组：
        for(String trade_id:trade_ids){
            sql.append("?,");
            valueList.add(trade_id);
        }
      //删除最后一个逗号
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        
		return C3p0Util.qr.execute(sql.toString(), valueList.toArray())>0;
	}

	@Override
	public List<Kiting> selectByName(String user_name, int pageNum, int pageSize) throws SQLException {
		List<Kiting> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select * from kiting_table ");
		if(user_name==null||user_name.equals("")) {
			sql.append(" order by trade_id desc limit ?,? ");
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<Kiting>(Kiting.class),(pageNum-1)*pageSize,pageSize);
		}else {
			sql.append(" where kiting_table.user_name=? order by trade_id desc limit ?,?");
			list = C3p0Util.qr.query(sql.toString(),new BeanListHandler<Kiting>(Kiting.class),user_name,(pageNum-1)*pageSize,pageSize);
		}
		return list;
	}

	/**
	 * 根据姓名搜索后的总条数
	 * @param user_name  查询的姓名
	 * @return
	 * @throws SQLException
	 */
	@Override
	public long selectTotal(String user_name) throws SQLException {
		long n = 0;
		if(user_name==null||("".equals(user_name))) {
			n= (long)C3p0Util.qr.query("select count(*) from kiting_table",new ScalarHandler<>());
		}else {
			/*String name = "%"+user_name+"%";*/
			n= (long)C3p0Util.qr.query("select count(*) from kiting_table where user_name like ?",new ScalarHandler<>(),user_name);
		}
		return n;
	}
	
	
	
}
